{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "import pandas as pd\n",
    "dt1 = ts.pro_bar('040651.SZ', adj='hfq', start_date='20050101', end_date='20050101')\n",
    "print(dt1 is None)\n",
    "print(type(dt1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "from sqlalchemy import create_engine \n",
    "import pandas as pd\n",
    "engine_ts = create_engine('mysql://snrmyi:oditsz@127.0.0.1:3306/stocks?charset=utf8&use_unicode=1')\n",
    "sql = \"SELECT * FROM stock_basic\"\n",
    "df = pd.read_sql_query(sql, engine_ts)\n",
    "print(df.iloc[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "from sqlalchemy import create_engine \n",
    "import pandas as pd\n",
    "pro = ts.pro_api('c72222b1f778c3a06b2f2f5a61634901453f36b0c1067d599b011529')\n",
    "engine_ts = create_engine('mysql://snrmyi:oditsz@127.0.0.1:3306/stocks?charset=utf8&use_unicode=1')\n",
    "df = pro.trade_cal(fields=[\"exchange\", \"cal_date\", \"is_open\", \"pretrade_date\"])\n",
    "print(df)\n",
    "res = df.to_sql('trade_cal', engine_ts, index=False, if_exists='append', chunksize=5000)\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "from sqlalchemy import create_engine \n",
    "import pandas as pd\n",
    "\n",
    "my_token = 'c72222b1f778c3a06b2f2f5a61634901453f36b0c1067d599b011529'\n",
    "ts.set_token(my_token)\n",
    "pro = ts.pro_api()\n",
    "engine_ts = create_engine('mysql://snrmyi:oditsz@127.0.0.1:3306/stocks?charset=utf8&use_unicode=1')\n",
    "df = pd.read_sql_query(\"SELECT * FROM stock_basic\", engine_ts)\n",
    "profit_2005_2024 = pd.DataFrame(columns=['name', 'ts_code', 'close_2005', 'close_2024', 'profit'])\n",
    "already_readed = pd.read_sql_query(\"SELECT * FROM profit_2005_2024\", engine_ts)\n",
    "for i in df.index:\n",
    "    if df.iloc[i]['ts_code'] not in already_readed['ts_code'].values:\n",
    "        print(f\"正在处理 {i}:{df.iloc[i]['name']}\\t\\t\", end='\\r')\n",
    "        val1 = ts.pro_bar(ts_code=df.iloc[i]['ts_code'], adj='hfq', start_date='20050104', end_date='20050104')\n",
    "        val2 = ts.pro_bar(ts_code=df.iloc[i]['ts_code'], adj='hfq', start_date='20241230', end_date='20241230')\n",
    "        if val1 is not None and val2 is not None and not val1.empty and not val2.empty:\n",
    "            profit_2005_2024.loc[len(profit_2005_2024)] = \\\n",
    "                [df.iloc[i]['name'],\n",
    "                df.iloc[i]['ts_code'],\n",
    "                val1.iloc[0]['close'],\n",
    "                val2.iloc[0]['close'],\n",
    "                val2.iloc[0]['close'] / val1.iloc[0]['close']]\n",
    "        else:\n",
    "            profit_2005_2024.loc[len(profit_2005_2024)] = \\\n",
    "                    [df.iloc[i]['name'],\n",
    "                    df.iloc[i]['ts_code'], 0, 0, 0]\n",
    "        ret = profit_2005_2024.to_sql('profit_2005_2024', engine_ts, index=False, if_exists='replace', chunksize=5000)\n",
    "        print(f\"{ret} \\t {df.iloc[i]['name']}\\t\\t\", end='\\r')\n",
    "    else:\n",
    "        print(f\"跳过 {i}:{df.iloc[i]['name']}\\t\\t\", end='\\r')\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "from sqlalchemy import create_engine \n",
    "import pandas as pd\n",
    "\n",
    "my_token = 'c72222b1f778c3a06b2f2f5a61634901453f36b0c1067d599b011529'\n",
    "ts.set_token(my_token)\n",
    "pro = ts.pro_api()\n",
    "engine_ts = create_engine('mysql://snrmyi:oditsz@127.0.0.1:3306/stocks?charset=utf8&use_unicode=1')\n",
    "df = pd.read_sql_query(\"SELECT * FROM stock_basic\", engine_ts)\n",
    "profit_2005_2024 = pd.DataFrame(columns=['name', 'ts_code', 'close_2005', 'close_2024', 'profit'])\n",
    "for i in df.index:\n",
    "    print(f\"正在处理 {i}:{df.iloc[i]['name']}\\t\\t\", end='\\r')\n",
    "    val1 = ts.pro_bar(ts_code=df.iloc[i]['ts_code'], adj='hfq', start_date='20140102', end_date='20140102')\n",
    "    val2 = ts.pro_bar(ts_code=df.iloc[i]['ts_code'], adj='hfq', start_date='20241230', end_date='20241230')\n",
    "    if val1 is not None and val2 is not None and not val1.empty and not val2.empty:\n",
    "        profit_2005_2024.loc[len(profit_2005_2024)] = \\\n",
    "            [df.iloc[i]['name'],\n",
    "            df.iloc[i]['ts_code'],\n",
    "            val1.iloc[0]['close'],\n",
    "            val2.iloc[0]['close'],\n",
    "            val2.iloc[0]['close'] / val1.iloc[0]['close']]\n",
    "    else:\n",
    "        profit_2005_2024.loc[len(profit_2005_2024)] = \\\n",
    "                [df.iloc[i]['name'],\n",
    "                df.iloc[i]['ts_code'], 0, 0, 0]\n",
    "    ret = profit_2005_2024.to_sql('profit_2014_2024', engine_ts, index=False, if_exists='replace', chunksize=5000)\n",
    "    print(f\"{ret} \\t {df.iloc[i]['name']}\\t\\t\", end='\\r')\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from db_model import *\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy import create_engine \n",
    "import tushare as ts\n",
    "import pandas as pd\n",
    "\n",
    "engine = create_engine('mysql://snrmyi:oditsz@localhost/stocks?charset=utf8&use_unicode=1')\n",
    "session = sessionmaker(bind=engine)()\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "pro = ts.pro_api('c72222b1f778c3a06b2f2f5a61634901453f36b0c1067d599b011529')\n",
    "df = pro.trade_cal(fields=[\"exchange\", \"cal_date\", \"is_open\", \"pretrade_date\"])\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    cal_old = session.query(trade_cal).filter_by(cal_date = row['cal_date']).first()\n",
    "    if cal_old is None:\n",
    "        cal = trade_cal(cal_date=row['cal_date'], exchange=row['exchange'], is_open=row['is_open'], pretrade_date=row['pretrade_date'])\n",
    "        session.add(cal)\n",
    "\n",
    "session.commit()   \n",
    "date = session.query(trade_cal).all()\n",
    "print(len(date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import akshare as ak\n",
    "\n",
    "stock_zh_index_spot_em_df = ak.stock_zh_index_spot_em(symbol=\"上证系列指数\")\n",
    "print(stock_zh_index_spot_em_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import akshare as ak\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
